# 导出文件

from sa.models import Project, Activity
from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status
from rest_framework.permissions import IsAuthenticated

from django.shortcuts import HttpResponse
from django.http import FileResponse

import csv
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Side, Border, PatternFill
from openpyxl.utils import get_column_letter

from io import BytesIO

from datetime import datetime
import logging

# 日志记录对象
logger = logging.getLogger('django')


class ProjectToFiles(APIView):
    """ 导出项目 """
    queryset = (Project.objects.all()
                .select_related('customer', 'creator')
                .prefetch_related('participants')
                .order_by('-id'))

    def get(self, request):
        roles = request.user.roles.values_list('role_name', flat=True)

        query_params = {}
        if len(roles) == 1 and 'SA_User' in roles:
            query_params['creator'] = request.user

        start_date = request.query_params.get('start_date')
        if start_date:
            # __gte 是 Django ORM 的一个查询查找器，表示“大于或等于”
            query_params['start_date__gte'] = start_date

        end_date = request.query_params.get('end_date')
        if end_date:
            # __lte 是 Django ORM 的一个查询查找器，表示“小于或等于”
            query_params['end_date__lte'] = end_date

        category = request.query_params.get('category')
        if category:
            query_params['category'] = category

        region = request.query_params.get('region')
        if region:
            query_params['creator__region'] = region

        status = request.query_params.get('status')
        if status:
            query_params['status'] = status
        print(query_params)
        queryset = self.queryset.filter(**query_params)

        # 导出到csv
        response = HttpResponse(
            # 类型csv, 不写就是html
            content_type="text/csv",
            # 指定headers, 附件
            headers={"Content-Disposition": 'attachment'},
        )
        # 在 请求标头的 User-Agent 里检查 是否有Windows字符，如果有改变码为utf-8-sig,
        # 否则 Windows系统 显示中文是乱码
        if 'Windows' in request.headers.get('User-Agent'):
            response.charset = 'utf-8-sig'
            # 构建一个csv 写入器，传入的参数为response
        writer = csv.writer(response)
        # 写入第一行内容
        writer.writerow(["SR#", '客户名称', "OwnerSA", '所属RO',
                         '大项目', '合同金额(K)', '参与者', "工作分类",
                         '故障等级', "software", '版本',
                         '服务器数量', 'MFD数量', '打印机数量',
                         '开始日期', '状态', '结束日期',
                         '总计用时（天）', '项目描述', '项目得分'])
        for obj in queryset:
            writer.writerow([
                obj.SR_No,
                obj.customer,
                obj.creator,
                obj.creator.region,
                "Yes" if obj.is_team_project else "No",
                obj.contract_amount,
                ';'.join(obj.participants.all().values_list('cn_name', flat=True)),
                obj.get_category_display(),
                obj.get_level_display(),
                obj.get_software_display(),
                obj.version,
                obj.server_num,
                obj.mfd_num,
                obj.printer_num,
                obj.start_date,
                obj.get_status_display(),
                obj.end_date,
                obj.duration,
                obj.description,
                obj.points if "SA_Manager" in roles else 'Not permission'
            ])
        return response

        # return Response(data=queryset, status=status.HTTP_200_OK)
        # return Response(data="OK", status=status.HTTP_200_OK)


class ActivityToFiles(APIView):
    """ 导出Activity """
    queryset = Activity.objects.all().select_related('project', 'creator').order_by('-id')

    def get(self, request):
        roles = request.user.roles.values_list('role_name', flat=True)
        query_params = {}
        if len(roles) == 1 and 'SA_User' in roles:
            query_params['creator'] = request.user

        activity_date = request.query_params.get('date')
        if activity_date:
            query_params['activity_date__gte'] = activity_date

        region = request.query_params.get('region')
        if region:
            query_params['creator__region'] = region

        category = request.query_params.get('category')
        if category:
            query_params['activity_type'] = category

        queryset = self.queryset.filter(**query_params)

        # 导出到csv
        response = HttpResponse(
            # 类型csv, 不写就是html
            content_type="text/csv",
            # 指定headers, 附件
            headers={"Content-Disposition": 'attachment'},
        )
        # 在 请求标头的 User-Agent 里检查 是否有Windows字符，如果有改变码为utf-8-sig,
        # 否则 Windows系统 显示中文是乱码
        if 'Windows' in request.headers.get('User-Agent'):
            response.charset = 'utf-8-sig'
            # 构建一个csv 写入器，传入的参数为response
        writer = csv.writer(response)
        # 写入第一行内容
        writer.writerow([
            "SR#", "Activity#", '客户名称', 'SA',
            '任务分类', '任务日期', '开始时间', '结束时间',
            '加班', '加班用时（分钟）', '总用时（分钟）',
            "软件类型",
            "服务器数量", 'MFD数量', '打印机数量',
            "工作描述", 'Points'
        ])
        for obj in queryset:
            writer.writerow([
                obj.project.SR_No,
                obj.activity_number,
                obj.project.customer.name,
                obj.creator,
                obj.get_activity_type_display(),
                obj.activity_date,
                obj.start_time,
                obj.end_time,
                "Yes" if obj.is_overtime else "No",
                obj.overtime,
                obj.total_time,
                obj.software_type,
                obj.task_server_num,
                obj.task_mfd_num,
                obj.task_printer_num,
                obj.description,
                obj.points if "SA_Manager" in roles else 'No permission'
            ])
        return response


# 使用 openpyxl
class ProjectAndActivity(APIView):
    """ 导出项目到 xlsx """
    project_queryset = (Project.objects.all()
                        .select_related('customer', 'creator')
                        .prefetch_related('participants')
                        .order_by('-id'))
    activity_queryset = (Activity.objects.all()
                         .select_related('project', 'creator')
                         .order_by('-id'))
    permission_classes = [IsAuthenticated]

    def post(self, request):
        logger.info(f'{request.user.cn_name} 准备导出数据')
        # 获取登录用户的角色信息列表
        roles = request.user.roles.values_list('role_name', flat=True)

        # 获取前端数据 并 检查类型
        try:
            project_filter = request.data.get('project', {})
            activity_filter = request.data.get('activity', {})
            # 激活显示的worksheet
            activeWorksheet = request.data.get('activeWorksheet', 0)
            # print(project_filter)
            # print(activity_filter)
            # print('activeWorksheet:', activeWorksheet)
            if not isinstance(project_filter, dict) or not isinstance(activity_filter, dict):
                # raise ValueError("过滤参数必须是字典类型")
                return Response(
                    data={'error': '无效的请求参数'},
                    status=status.HTTP_400_BAD_REQUEST
                )
        except Exception as e:
            logger.error(f'请求参数错误：{str(e)}')
            return Response(
                data={'error': '无效的请求参数'},
                status=status.HTTP_400_BAD_REQUEST
            )

        # 生成两空查询集
        project_query_params = {}
        activity_query_params = {}

        if len(roles) == 1 and 'SA_User' in roles:
            project_query_params['creator'] = request.user
            activity_query_params['creator'] = request.user

        # 处理 project 的 query params
        start_date = project_filter.get('start_date')
        if start_date:
            # __gte 是 Django ORM 的一个查询查找器，表示“大于或等于”
            project_query_params['start_date__gte'] = start_date

        end_date = project_filter.get('end_date')
        if end_date:
            # __lte 是 Django ORM 的一个查询查找器，表示“小于或等于”
            project_query_params['end_date__lte'] = end_date

        category = project_filter.get('category')
        if category:
            project_query_params['category'] = category

        region = project_filter.get('region')
        if region:
            project_query_params['creator__region'] = region

        get_status = project_filter.get('status')
        if get_status:
            project_query_params['status'] = get_status
        # 过滤查询参数
        # print(project_query_params)
        project_queryset = self.project_queryset.filter(**project_query_params)

        # 处理Activity 的 query params
        activity_date = activity_filter.get('date')
        if activity_date:
            activity_query_params['activity_date__gte'] = activity_date

        region = activity_filter.get('region')
        if region:
            activity_query_params['creator__region'] = region

        category = activity_filter.get('category')
        if category:
            activity_query_params['activity_type'] = category

        # print(activity_query_params)
        activity_queryset = self.activity_queryset.filter(**activity_query_params)

        # 创建工作簿和工作表
        wb = Workbook()
        del wb['Sheet']  # 删除默认Sheet
        wb.create_sheet('Project', 0)
        wb.create_sheet('Activity', 1)
        wb.active = activeWorksheet  # 激活显示的worksheet

        # 获取工作表对象（两种等效方式）
        ws_project = wb.worksheets[0]
        ws_activity = wb['Activity']

        # 设置project表的表头
        project_header = ["SR#", '客户名称', "SA", 'RO',
                          '大项目', '金额(K)', '参与者', "工作分类",
                          '故障等级', "software", '版本',
                          '服务器', 'MFD', '打印机',
                          '开始日期', '状态', '结束日期',
                          '合计天数', '项目描述', '项目得分']
        ws_project.append(project_header)

        # 增加两个空行
        ws_project.insert_rows(1, 2)
        # 根据表头长度合并单元格
        ws_project.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(project_header))
        # 设置第一行的行高
        ws_project.row_dimensions[1].height = 30
        # 获得每列宽度: 单位是"字符宽度"，1个英文字符≈1单位，中文字符≈2单位
        project_column_widths = {
            'A': 10,  # SR#
            'B': 28,  # 客户名称
            'C': 8,  # SA
            'D': 5,  # RO
            'E': 7,  # 大项目
            'F': 8,  # 金额
            'G': 14,  # 参与者
            'H': 9,  # 工作分类
            'I': 9,  # 故障等级
            'J': 16,  # software
            'K': 6,  # 版本
            'L': 8,  # 服务器
            'M': 7,  # MFD
            'N': 8,  # 打印机
            'O': 12,  # 开始日期
            'P': 7,  # 状态
            'Q': 12,  # 结束日期
            'R': 9,  # 总计用时天
            'S': 40,  # 描述
            'T': 9  # 得分
        }
        # print(type(project_column_widths))
        # print(project_column_widths)
        for column_letter, width in project_column_widths.items():  # 遍历字典设置宽度
            ws_project.column_dimensions[column_letter].width = width

        # 设置字体样式
        title_font = Font('微软雅黑', size=15, bold=True, color='0099FF')
        header_font = Font('微软雅黑', size=10, bold=True, color='000000')
        # 设置对齐
        title_Alignment = Alignment(
            # horizontal='center',    # 水平居中对齐
            vertical='center',  # 垂直居中对齐
            wrap_text=True,  # 自动换行
            shrink_to_fit=False,  # 不缩小字体填充
            indent=0  # 无缩进
        )
        header_Alignment = Alignment(
            # horizontal='center',    # 水平居中对齐
            vertical='center',  # 垂直居中对齐
            wrap_text=True,  # 自动换行
            shrink_to_fit=False,  # 不缩小字体填充
        )
        # 设置边框 side + border  一起使用
        head_side = Side(style='thin', color='000000')
        # header填充
        header_fill = PatternFill(patternType='solid', fgColor="CCCCCC")

        ws_project['a1'].font = title_font
        ws_project['a1'].alignment = title_Alignment
        ws_project['a1'].fill = header_fill
        # 为header设置边框
        start_col = 1  # 开始列A
        end_col = len(project_header)  # 终止列T
        row_num = 3  # 第三行
        for col_idx in range(start_col, end_col + 1):  # 遍历出每个单元格cell
            cell = ws_project.cell(row=row_num, column=col_idx)  # cell(行, 列)
            cell.font = header_font  # 字体
            cell.alignment = header_Alignment  # 样式
            cell.fill = header_fill
            # 根据单元格位置设置不同的边框
            if col_idx == start_col:
                cell.border = Border(
                    top=head_side,
                    left=head_side,
                    bottom=head_side
                )
            elif col_idx == end_col:
                cell.border = Border(
                    top=head_side,
                    right=head_side,
                    bottom=head_side
                )
            else:
                cell.border = Border(
                    top=head_side,
                    bottom=head_side
                )
        # 增加筛选
        header_row = 3
        max_row = ws_project.max_row  # 最大行
        max_column = ws_project.max_column  # 最大列
        max_column_letter = get_column_letter(max_column)  # 将数字列索引转换为 Excel 风格的字母列标
        ws_project.auto_filter.ref = f"A{header_row}:{max_column_letter}{max_row}"  # 例如 A3:T50

        # 写入标题内容
        ws_project['a1'] = f"{start_date} 至 {end_date} 项目统计报表"

        # 填充数据
        # join() 是字符串（str）的一个方法，用于将一个字符串序列（如列表、元组等）连接成一个新的字符串。
        # 分隔符.join(可迭代对象)
        # 分隔符：一个字符串，用于指定连接时的分隔符（可以是空字符串 ""）。
        # 可迭代对象：必须是一个包含字符串元素的序列（如列表 list、元组 tuple 等）。
        for project in project_queryset:
            ws_project.append([
                project.SR_No,
                project.customer.name,
                project.creator.cn_name,
                project.creator.region,
                'YES' if project.is_team_project else 'NO',
                project.contract_amount,
                ';'.join(project.participants.all().values_list('cn_name', flat=True)),
                project.get_category_display(),
                project.get_level_display(),
                project.get_software_display(),
                project.version,
                project.server_num,
                project.mfd_num,
                project.printer_num,
                project.start_date,
                project.get_status_display(),
                project.end_date,
                project.duration,
                project.description,
                project.points if 'SA_Manager' in roles else 'No permission'
            ])

        # 处理 activity sheet
        # 设置Activity表的表头
        activity_header = ["SR#", "Activity#", '客户名称', 'SA',
                           '任务分类', '任务日期', '开始时间', '结束时间',
                           '加班', '加班用时', '总用时',
                           "software",
                           "服务器", 'MFD', '打印机',
                           "工作描述", 'Points']
        ws_activity.append(activity_header)
        # 在第1行前增加2空行，表头变成第3行
        ws_activity.insert_rows(1, 2)
        # 合并列，
        ws_activity.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(activity_header))
        # 设置第1行行高
        ws_activity.row_dimensions[1].height = 30
        # 设置每列列宽
        activity_column_widths = {
            "A": 10,  # SR#
            "B": 12,  # Activity#
            "C": 28,  # 客户名称
            "D": 8,  # SA
            "E": 9,  # 任务分类
            "F": 11,  # 任务日期
            "G": 10,  # 开始时间
            "H": 10,  # 结束时间
            "I": 7,  # 加班
            "J": 10,  # 加班用时
            "K": 8,  # 总用时
            "L": 16,  # software
            "M": 7,  # 服务器
            "N": 7,  # MFD
            "O": 8,  # 打印机
            "P": 40,  # 工作描述
            "Q": 9  # Points
        }
        # 遍历字典设置宽度
        for letter, width in activity_column_widths.items():
            ws_activity.column_dimensions[letter].width = width
        # 设置字体
        ws_activity['a1'].font = title_font
        # 设置对齐
        ws_activity['a1'].alignment = title_Alignment
        # 设置填充
        ws_activity['a1'].fill = header_fill
        # 为activity sheet 的 header 设置边框
        activity_start_col = 1  # 开始列
        activity_end_col = len(activity_header)  # 终止列
        activity_row_num = 3  # 第三行
        for col_idx in range(activity_start_col, activity_end_col + 1):
            cell = ws_activity.cell(row=activity_row_num, column=col_idx)
            cell.font = header_font  # 字体
            cell.alignment = header_Alignment  # 样式
            cell.fill = header_fill  # 填充
            # 不同单元格设置不同边框
            if col_idx == activity_start_col:
                cell.border = Border(
                    top=head_side,
                    left=head_side,
                    bottom=head_side
                )
            elif col_idx == activity_end_col:
                cell.border = Border(
                    top=head_side,
                    right=head_side,
                    bottom=head_side
                )
            else:
                cell.border = Border(
                    top=head_side,
                    bottom=head_side
                )
        # 增加筛选
        activity_max_row = ws_activity.max_row      # 获取sheet 的最大行
        activity_max_column = ws_activity.max_column    # 获取 sheet 的最大列
        activity_max_column_letter = get_column_letter(activity_max_column)     # 将数字列标转化为字母列标
        # auto_filter.ref = 'A3:H15'
        ws_activity.auto_filter.ref = f"A{header_row}:{activity_max_column_letter}{activity_max_row}"

        # 写入标题
        ws_activity['a1'] = f"{activity_date} 开始的 Activity"

        # 填充数据
        for activity in activity_queryset:
            ws_activity.append([
                activity.project.SR_No,
                activity.activity_number,
                activity.project.customer.name,
                activity.creator.cn_name,
                activity.get_activity_type_display(),
                activity.activity_date,
                activity.start_time,
                activity.end_time,
                "YES" if activity.is_overtime else "NO",
                activity.overtime,
                activity.total_time,
                activity.software_type,
                activity.task_server_num,
                activity.task_mfd_num,
                activity.task_printer_num,
                activity.description,
                activity.points if "SA_Manager" in roles else "No Permission"
            ])

        # wb.save('testxlsx.xlsx')  # 直接保存到本地

        # 保存到内存
        buffer = BytesIO()  # 创建内存缓冲区 类似创建一个虚拟的二进制文件
        wb.save(buffer)  # 将Excel文件写入内存 ,数据写入后，指针停在末尾
        buffer.seek(0)  # 移动指针到缓冲区开头，确保能读取全部内容, 否则读取的是空数据）

        now = datetime.now().strftime("%Y-%m-%d")  # 获取当前日期，格式化为 YYYY-MM-DD
        filename = f"{now}_工作记录.xlsx"
        # 生成带文件内容的响应
        response = FileResponse(
            buffer,  # 从内存读取数据
            as_attachment=True,
            filename=filename,  # 使用变量，不能直接引入语法语句
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'  # 明确指定xlsx类型
        )

        logger.info(f'{request.user.cn_name} 成功导出数据')
        return response
